﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EZCRS.HCMS.Entity;
using EZCRS.HCMS.Data;
using System.Data.SqlClient;
using System.Data;

namespace EZCRS.HCMS.SqlClient
{
    public class sql_hcrs_air
    {
        private string sqlstr = "";
        /// <summary>
        /// 新增或修改一条信息
        /// </summary>
        /// <param name="_h_roomcode"></param>
        /// <returns></returns>
        public bool insertorupdate(HCRS_AIR _hcrs_air)
        {
            bool result = true;
            switch (_hcrs_air.Level)
            {
                case "P"://酒店级，如年份、酒店编号存在，则更新，否则新增
                    if (_hcrs_air.Property_id != null && _hcrs_air.Year != null)
                    {
                        if (ExecSql.GetDataSet("select * from HCRS_AIR where property_id=" + _hcrs_air.Property_id + " and level='P' and year=" + _hcrs_air.Year).Tables[0].Rows.Count > 0)
                        {
                            if (Update(_hcrs_air, "property_id=" + _hcrs_air.Property_id + " and level='P' and year=" + _hcrs_air.Year) > 0)
                                result = true;
                            else
                                result = false;
                        }
                        else
                        {
                            if (Add(_hcrs_air) > 0)
                                result = true;
                            else
                                result = false;
                        }
                    }
                    else
                    {
                        result = false;
                    }
                    break;
                case "R"://房型级，如年份、酒店编号、房型代码存在，则更新，否则新增
                    if (_hcrs_air.Property_id != null && _hcrs_air.Year != null && _hcrs_air.RoomCode != "")
                    {
                        if (ExecSql.GetDataSet("select * from HCRS_AIR where property_id=" + _hcrs_air.Property_id + " and level='R' and year=" + _hcrs_air.Year + " and RoomCode='" + _hcrs_air.RoomCode + "'").Tables[0].Rows.Count > 0)
                        {
                            if (Update(_hcrs_air, "property_id=" + _hcrs_air.Property_id + " and level='R' and year=" + _hcrs_air.Year + " and RoomCode='" + _hcrs_air.RoomCode + "'") > 0)
                                result = true;
                            else
                                result = false;
                        }
                        else
                        {
                            if (Add(_hcrs_air) > 0)
                                result = true;
                            else
                                result = false;
                        }
                    }
                    else
                    {
                        result = false;
                    }
                    break;
                case "L"://计划级，如年份、酒店编号、房型代码、计划代码存在，则更新，否则新增
                    if (_hcrs_air.Property_id != null && _hcrs_air.Year != null && _hcrs_air.RoomCode != "" && _hcrs_air.PlanCode != "")
                    {
                        if (ExecSql.GetDataSet("select * from HCRS_AIR where property_id=" + _hcrs_air.Property_id + " and level='L' and year=" + _hcrs_air.Year + " and RoomCode='" + _hcrs_air.RoomCode + "' and PlanCode='" + _hcrs_air.PlanCode + "'").Tables[0].Rows.Count > 0)
                        {
                            if (Update(_hcrs_air, "property_id=" + _hcrs_air.Property_id + " and level='L' and year=" + _hcrs_air.Year + " and RoomCode='" + _hcrs_air.RoomCode + "' and PlanCode='" + _hcrs_air.PlanCode + "'") > 0)
                                result = true;
                            else
                                result = false;
                        }
                        else
                        {
                            if (Add(_hcrs_air) > 0)
                                result = true;
                            else
                                result = false;
                        }
                    }
                    else
                    {
                        result = false;
                    }
                    break;
            }
            return result;
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(HCRS_AIR model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into HCRS_AIR(");
            strSql.Append("Property_id,Level,RoomCode,PlanCode,Source,Year,January_AI,February_AI,March_AI,April_AI,May_AI,June_AI,July_AI,August_AI,September_AI,October_AI,November_AI,December_AI,January_R,February_R,March_R,April_R,May_R,June_R,July_R,August_R,September_R,October_R,November_R,December_R)");
            strSql.Append(" values (");
            strSql.Append("@Property_id,@Level,@RoomCode,@PlanCode,@Source,@Year,@January_AI,@February_AI,@March_AI,@April_AI,@May_AI,@June_AI,@July_AI,@August_AI,@September_AI,@October_AI,@November_AI,@December_AI,@January_R,@February_R,@March_R,@April_R,@May_R,@June_R,@July_R,@August_R,@September_R,@October_R,@November_R,@December_R)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@Property_id", SqlDbType.Int,4),
					new SqlParameter("@Level", SqlDbType.VarChar,10),
					new SqlParameter("@RoomCode", SqlDbType.VarChar,50),
					new SqlParameter("@PlanCode", SqlDbType.VarChar,50),
					new SqlParameter("@Source", SqlDbType.VarChar,20),
					new SqlParameter("@Year", SqlDbType.Int,4),
					new SqlParameter("@January_AI", SqlDbType.Binary,50000),
					new SqlParameter("@February_AI", SqlDbType.Binary,50000),
					new SqlParameter("@March_AI", SqlDbType.Binary,50000),
					new SqlParameter("@April_AI", SqlDbType.Binary,50000),
					new SqlParameter("@May_AI", SqlDbType.Binary,50000),
					new SqlParameter("@June_AI", SqlDbType.Binary,50000),
					new SqlParameter("@July_AI", SqlDbType.Binary,50000),
					new SqlParameter("@August_AI", SqlDbType.Binary,50000),
					new SqlParameter("@September_AI", SqlDbType.Binary,50000),
					new SqlParameter("@October_AI", SqlDbType.Binary,50000),
					new SqlParameter("@November_AI", SqlDbType.Binary,50000),
					new SqlParameter("@December_AI", SqlDbType.Binary,50000),
					new SqlParameter("@January_R", SqlDbType.Binary,50000),
					new SqlParameter("@February_R", SqlDbType.Binary,50000),
					new SqlParameter("@March_R", SqlDbType.Binary,50000),
					new SqlParameter("@April_R", SqlDbType.Binary,50000),
					new SqlParameter("@May_R", SqlDbType.Binary,50000),
					new SqlParameter("@June_R", SqlDbType.Binary,50000),
					new SqlParameter("@July_R", SqlDbType.Binary,50000),
					new SqlParameter("@August_R", SqlDbType.Binary,50000),
					new SqlParameter("@September_R", SqlDbType.Binary,50000),
					new SqlParameter("@October_R", SqlDbType.Binary,50000),
					new SqlParameter("@November_R", SqlDbType.Binary,50000),
					new SqlParameter("@December_R", SqlDbType.Binary,50000)};
            parameters[0].Value = model.Property_id;
            parameters[1].Value = model.Level;
            parameters[2].Value = model.RoomCode;
            parameters[3].Value = model.PlanCode;
            parameters[4].Value = model.Source;
            parameters[5].Value = model.Year;
            parameters[6].Value = model.January_AI;
            parameters[7].Value = model.February_AI;
            parameters[8].Value = model.March_AI;
            parameters[9].Value = model.April_AI;
            parameters[10].Value = model.May_AI;
            parameters[11].Value = model.June_AI;
            parameters[12].Value = model.July_AI;
            parameters[13].Value = model.August_AI;
            parameters[14].Value = model.September_AI;
            parameters[15].Value = model.October_AI;
            parameters[16].Value = model.November_AI;
            parameters[17].Value = model.December_AI;
            parameters[18].Value = model.January_R;
            parameters[19].Value = model.February_R;
            parameters[20].Value = model.March_R;
            parameters[21].Value = model.April_R;
            parameters[22].Value = model.May_R;
            parameters[23].Value = model.June_R;
            parameters[24].Value = model.July_R;
            parameters[25].Value = model.August_R;
            parameters[26].Value = model.September_R;
            parameters[27].Value = model.October_R;
            parameters[28].Value = model.November_R;
            parameters[29].Value = model.December_R;

            return ExecSql.SqlExecNoquery(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(HCRS_AIR model, string strwhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update HCRS_AIR set ");
            strSql.Append("Property_id=@Property_id,");
            strSql.Append("Level=@Level,");
            strSql.Append("RoomCode=@RoomCode,");
            strSql.Append("PlanCode=@PlanCode,");
            strSql.Append("Source=@Source,");
            strSql.Append("Year=@Year,");
            strSql.Append("January_AI=@January_AI,");
            strSql.Append("February_AI=@February_AI,");
            strSql.Append("March_AI=@March_AI,");
            strSql.Append("April_AI=@April_AI,");
            strSql.Append("May_AI=@May_AI,");
            strSql.Append("June_AI=@June_AI,");
            strSql.Append("July_AI=@July_AI,");
            strSql.Append("August_AI=@August_AI,");
            strSql.Append("September_AI=@September_AI,");
            strSql.Append("October_AI=@October_AI,");
            strSql.Append("November_AI=@November_AI,");
            strSql.Append("December_AI=@December_AI,");
            strSql.Append("January_R=@January_R,");
            strSql.Append("February_R=@February_R,");
            strSql.Append("March_R=@March_R,");
            strSql.Append("April_R=@April_R,");
            strSql.Append("May_R=@May_R,");
            strSql.Append("June_R=@June_R,");
            strSql.Append("July_R=@July_R,");
            strSql.Append("August_R=@August_R,");
            strSql.Append("September_R=@September_R,");
            strSql.Append("October_R=@October_R,");
            strSql.Append("November_R=@November_R,");
            strSql.Append("December_R=@December_R");
            strSql.Append(" where " + strwhere + " ");
            SqlParameter[] parameters = {
					new SqlParameter("@Property_id", SqlDbType.Int,4),
					new SqlParameter("@Level", SqlDbType.VarChar,10),
					new SqlParameter("@RoomCode", SqlDbType.VarChar,50),
					new SqlParameter("@PlanCode", SqlDbType.VarChar,50),
					new SqlParameter("@Source", SqlDbType.VarChar,20),
					new SqlParameter("@Year", SqlDbType.Int,4),
					new SqlParameter("@January_AI", SqlDbType.Binary,50000),
					new SqlParameter("@February_AI", SqlDbType.Binary,50000),
					new SqlParameter("@March_AI", SqlDbType.Binary,50000),
					new SqlParameter("@April_AI", SqlDbType.Binary,50000),
					new SqlParameter("@May_AI", SqlDbType.Binary,50000),
					new SqlParameter("@June_AI", SqlDbType.Binary,50000),
					new SqlParameter("@July_AI", SqlDbType.Binary,50000),
					new SqlParameter("@August_AI", SqlDbType.Binary,50000),
					new SqlParameter("@September_AI", SqlDbType.Binary,50000),
					new SqlParameter("@October_AI", SqlDbType.Binary,50000),
					new SqlParameter("@November_AI", SqlDbType.Binary,50000),
					new SqlParameter("@December_AI", SqlDbType.Binary,50000),
					new SqlParameter("@January_R", SqlDbType.Binary,50000),
					new SqlParameter("@February_R", SqlDbType.Binary,50000),
					new SqlParameter("@March_R", SqlDbType.Binary,50000),
					new SqlParameter("@April_R", SqlDbType.Binary,50000),
					new SqlParameter("@May_R", SqlDbType.Binary,50000),
					new SqlParameter("@June_R", SqlDbType.Binary,50000),
					new SqlParameter("@July_R", SqlDbType.Binary,50000),
					new SqlParameter("@August_R", SqlDbType.Binary,50000),
					new SqlParameter("@September_R", SqlDbType.Binary,50000),
					new SqlParameter("@October_R", SqlDbType.Binary,50000),
					new SqlParameter("@November_R", SqlDbType.Binary,50000),
					new SqlParameter("@December_R", SqlDbType.Binary,50000)};
            parameters[0].Value = model.Property_id;
            parameters[1].Value = model.Level;
            parameters[2].Value = model.RoomCode;
            parameters[3].Value = model.PlanCode;
            parameters[4].Value = model.Source;
            parameters[5].Value = model.Year;
            parameters[6].Value = model.January_AI;
            parameters[7].Value = model.February_AI;
            parameters[8].Value = model.March_AI;
            parameters[9].Value = model.April_AI;
            parameters[10].Value = model.May_AI;
            parameters[11].Value = model.June_AI;
            parameters[12].Value = model.July_AI;
            parameters[13].Value = model.August_AI;
            parameters[14].Value = model.September_AI;
            parameters[15].Value = model.October_AI;
            parameters[16].Value = model.November_AI;
            parameters[17].Value = model.December_AI;
            parameters[18].Value = model.January_R;
            parameters[19].Value = model.February_R;
            parameters[20].Value = model.March_R;
            parameters[21].Value = model.April_R;
            parameters[22].Value = model.May_R;
            parameters[23].Value = model.June_R;
            parameters[24].Value = model.July_R;
            parameters[25].Value = model.August_R;
            parameters[26].Value = model.September_R;
            parameters[27].Value = model.October_R;
            parameters[28].Value = model.November_R;
            parameters[29].Value = model.December_R;

            return ExecSql.SqlExecNoquery(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="page"></param>
        /// <param name="strwhere">查询条件</param>
        /// <param name="pagecount">返回页数总数</param>
        /// <returns></returns>
        public List<HCRS_AIR> getallbypage(int page, string strwhere, out int pagecount)
        {

            int recordcount = 0;
            pagecount = 1;
            List<HCRS_AIR> _log = ExecSql.GetTableList<HCRS_AIR>("HCRS_AIR", "*", " property_id desc ", "id", 20, page, strwhere, out recordcount);
            pagecount = CFun.pagecount(recordcount, 20);
            return _log;
        }


        /// <summary>
        /// 返回所有符合条件的信息
        /// </summary>
        /// <param name="strwhere">条件</param>
        /// <returns></returns>
        public List<HCRS_AIR> getall(string strwhere)
        {
            if (strwhere != "")
                sqlstr = "select * from HCRS_AIR where " + strwhere;
            else
                sqlstr = "select * from HCRS_AIR ";
            try
            {
                return ExecSql.GetTableList<HCRS_AIR>(sqlstr);
            }
            catch { return null; }
        }

        /// <summary>
        /// 返回一条信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public HCRS_AIR getitem(int id)
        {
            try
            {
                return ExecSql.GetTableList<HCRS_AIR>("select * from HCRS_AIR where id=" + id.ToString() + "")[0];
            }
            catch { return null; }
        }

        #region 删除
        /// <summary>
        /// 删除一条信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool deleteitem(int id)
        {
            return (ExecSql.SqlExecNoquery("delete from HCRS_AIR where id=" + id.ToString()) > 0) ? true : false;
        }

        /// <summary>
        /// 删除一批数据
        /// </summary>
        /// <param name="idlist"></param>
        /// <returns></returns>
        public bool deleteitem(string idlist)
        {
            return (ExecSql.SqlExecNoquery("delete from HCRS_AIR where id in (" + idlist + ")") > 0) ? true : false;
        }
        #endregion
    }
}